﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
/// <summary>
/// Summary description for DataConnection
/// </summary>
public class DataConnection
{
	public DataConnection()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    SqlConnection sqlCnection = new SqlConnection();
    SqlCommand sqlCmand = new SqlCommand();
    void OpenConnection()
    {
        if (sqlCnection.State==ConnectionState.Closed)
        {
            //sqlCnection.ConnectionString = "Data Source=.\\SQLEXPRESS;attachDbFilename=|datadirectory|\\QLCuaHangCD.mdf;Integrated Security=True;User Instance=True";
            sqlCnection.ConnectionString = "server=NguyenNhuNgoc\\SQLEXPRESS;database=QLCuaHangCD;integrated security=true";
            sqlCnection.Open();
        }
    }
    void CloseConnection()
    {
        sqlCnection.Close();
    }
    public DataTable GetData(string sql, params SqlParameter[] pmeter)
    {
        sqlCmand.Connection = sqlCnection;
        sqlCmand.CommandText = sql;
        sqlCmand.CommandType = CommandType.Text;

        sqlCmand.Parameters.Clear();
        sqlCmand.Parameters.AddRange(pmeter);

        OpenConnection();
        SqlDataReader sqlReader = sqlCmand.ExecuteReader();
        DataTable dtable = new DataTable();
        dtable.Load(sqlReader);
        sqlReader.Close();
        CloseConnection();
        return dtable;
    } 
    public DataTable GetData_SP(string proce, params SqlParameter[] pmeter)
    {
        sqlCmand.Connection = sqlCnection;
        sqlCmand.CommandText = proce;
        sqlCmand.CommandType = CommandType.StoredProcedure;

        sqlCmand.Parameters.Clear();
        sqlCmand.Parameters.AddRange(pmeter);

        OpenConnection();
        SqlDataReader sqlReader = sqlCmand.ExecuteReader();
        DataTable dtable = new DataTable();
        dtable.Load(sqlReader);
        sqlReader.Close();
        CloseConnection();
        return dtable;
    }
    //public object LoadData(string sql, params SqlParameter[] pmeter)
    //{
    //    sqlCmand.Connection = sqlCnection;
    //    sqlCmand.CommandText = sql;
    //    sqlCmand.CommandType = CommandType.Text;

    //    sqlCmand.Parameters.Clear();
    //    sqlCmand.Parameters.AddRange(pmeter);

    //    OpenConnection();
    //    object oj = sqlCmand.ExecuteScalar();
    //    CloseConnection();
    //    return oj;
    //}
    //public void Execution(string ProceName,params SqlParameter[] pmeter)
    //{
    //    sqlCmand.Connection = sqlCnection;
    //    sqlCmand.CommandText = ProceName;
    //    sqlCmand.CommandType = CommandType.StoredProcedure;

    //    sqlCmand.Parameters.Clear();
    //    sqlCmand.Parameters.AddRange(pmeter);

    //    OpenConnection();
    //    sqlCmand.ExecuteNonQuery();
    //    CloseConnection();
    //}
    public void thuchienlenh(string sql, params SqlParameter[] pars)
    {
        sqlCmand.Connection = sqlCnection;
        sqlCmand.CommandText = sql;
        sqlCmand.CommandType = CommandType.Text;
        sqlCmand.Parameters.Clear();
        sqlCmand.Parameters.AddRange(pars);

        OpenConnection();
        sqlCmand.ExecuteNonQuery();
        CloseConnection();
    }
}
